package cn.fanxin.tools;

import cn.fanxin.enums.CheckColEnum;
import cn.fanxin.enums.DateTypeEnum;
import cn.fanxin.enums.JsTypeEnum;
import cn.fanxin.utils.MyJdbcUtils;
import com.mysql.jdbc.StringUtils;
import java.sql.Connection;
import java.util.*;

/**
 * @Author: china-fanxin
 * @Website: https://fan-xin.cn
 * @Date: 2022/11/24 14:24
 * @Description: 检查格式，智能修正格式的类
 */
public class CommonCheck {

    public static Boolean isRelationMap = Boolean.FALSE;
    public static List<String> dstColList = new ArrayList<String>();
    public static HashMap<String, HashMap<Integer, String>> etlConfMap = new HashMap<>();

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 大体检测指标配置中可能存在的配置出错，缺配、漏配，调度参数出错等问题
     * @Date: 2022/11/25 14:26
     * @Param: [acctDate, kpiConfMap]
     * @return: void
     */
    public static void checkJsAll(String acctDate,HashMap<String,String> kpiConfMap,Properties confProp){
        checkKpiDataType(acctDate,kpiConfMap);
        checkKpiConf(kpiConfMap);
        checkNotNull(kpiConfMap);
        checkUpperCase(kpiConfMap);
        checkIsValid(kpiConfMap);
        checkRelationMap(kpiConfMap,confProp);
        if (isRelationMap){
            //计算指标永远免配置
        }else {
            throw new MyException("\n计算指标的来源表和目标表应当相同，即它所依赖的基础指标的目标表。");
        }
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description:
     * @Date: 2022/11/28 1:09
     * @Param: [acctDate, kpiConfMap, etlConfMap]
     * @return: void
     */
    public static void checkBaseAll(String acctDate, HashMap<String,String> kpiConfMap, Properties confProp){
        checkKpiDataType(acctDate,kpiConfMap);
        checkKpiConf(kpiConfMap);
        checkNotNull(kpiConfMap);
        checkUpperCase(kpiConfMap);
        checkIsValid(kpiConfMap);
        checkRelationMap(kpiConfMap,confProp);
        //检测元数据匹配度
        if (isRelationMap){
            //通过元数据检测自动进行了字段间的映射，免配置ETL表

        }else {
            etlConfMap = CommonFun.getEtlConf(confProp,kpiConfMap);
            //若无法自动匹配元数据，则需要人为配置ETL表
            checkEtlCol(acctDate, kpiConfMap, etlConfMap);
        }
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 检测MC上来源表和目标表的字段映射关系
     * @Date: 2022/11/28 1:09
     * @Param: [acctDate, kpiConfMap, etlConfMap]
     * @return: void
     */
    public static void checkMcBaseAll(String acctDate, HashMap<String,String> kpiConfMap, Properties confProp){
        checkKpiDataType(acctDate,kpiConfMap);
        checkKpiConf(kpiConfMap);
        checkNotNull(kpiConfMap);
        checkUpperCase(kpiConfMap);
        checkIsValid(kpiConfMap);
        checkMcRelationMap(kpiConfMap,confProp);
        //检测元数据匹配度
        if (isRelationMap){
            //通过元数据检测自动进行了字段间的映射，免配置ETL表

        }else {
            etlConfMap = CommonFun.getEtlConf(confProp,kpiConfMap);
            //若无法自动匹配元数据，则需要人为配置ETL表
            checkEtlCol(acctDate, kpiConfMap, etlConfMap);
        }
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 检查计算公式配置情况，并自动转换为标准格式
     * @Date: 2022/11/24 15:16
     * @Param: [formular]
     * @return: java.lang.String
     */
    public static String checkAndFormatFormular(String formular){
        String kpiCode = CommonFun.getSplitByIndex(formular,0);
        String jsType = CommonFun.getSplitByIndex(formular,1);
        String year = CommonFun.getSplitByIndex(formular,2);
        String month = CommonFun.getSplitByIndex(formular,3);
        String day = CommonFun.getSplitByIndex(formular,4);
        if (jsType.isEmpty()){
            jsType = "NOR";
        }else if (!JsTypeEnum.toList().contains(jsType)){
            throw new MyException("\n计算公式配置问题：\n不支持的计算类型："+jsType);
        }
        if (year.isEmpty()){
            year = "0";
        }else if (CommonFun.isNumber(year)){
            try {
                if (Integer.valueOf(year)<=0){
                    year = year;
                }else {
                    throw new MyException("\n计算公式配置问题：\n年移动位应为小于等于0的整数");
                }
            }catch (Exception e){
                throw new MyException("\n计算公式配置问题：\n年移动位应为小于等于0的整数");
            }
        }else {
            throw new MyException("\n计算公式配置问题：\n年移动位只允许使用数字，不支持使用:"+year);
        }
        if (month.isEmpty()){
            month = "0";
        }else if (CommonFun.isNumber(month)){
            try {
                if (Integer.valueOf(month)<=0){
                    month = month;
                }else {
                    throw new MyException("\n计算公式配置问题：\n月移动位应为小于等于0的整数，或支持的特殊移动位："+DateTypeEnum.toDefString());
                }
            }catch (Exception e){
                throw new MyException("\n计算公式配置问题：\n月移动位应为小于等于0的整数，或支持的特殊移动位："+DateTypeEnum.toDefString());
            }
        }else if (DateTypeEnum.toDefString().contains(month)){
            month = month;
        }else {
            throw new MyException("\n计算公式配置问题：\n月移动位只允许使用数字，不支持使用:"+month);
        }
        if (day.isEmpty()){
            day = "0";
        }else if (CommonFun.isNumber(day)){
            try {
                if (Integer.valueOf(day)<=0){
                    day = day;
                }else {
                    throw new MyException("\n计算公式配置问题：\n日移动位应为小于等于0的整数，或支持的特殊移动位："+DateTypeEnum.toDefString());
                }
            }catch (Exception e){
                throw new MyException("\n计算公式配置问题：\n日移动位应为小于等于0的整数，或支持的特殊移动位："+DateTypeEnum.toDefString());
            }
        }else if (DateTypeEnum.toDefString().contains(day)){
            day = day;
        }else {
            throw new MyException("\n计算公式配置问题：\n日移动位只允许使用数字，不支持使用:"+day);
        }
        return kpiCode+","+jsType+","+year+","+month+","+day;
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 检测指标配置表中的信息和调度加工传入的账期参数格式是否匹配
     * @Date: 2022/11/25 0:58
     * @Param: [acctDate, kpiConfMap]
     * @return: void
     */
    public static void checkKpiDataType(String acctDate, HashMap<String,String> kpiConfMap){
        String kpiType = kpiConfMap.get("COMP_KPI_DATA_TYPE");
        if (acctDate.length()==6){
            if ("D".equals(kpiType)){
                throw new MyException("\n指标调度错误或指标配置错误，该指标在配置表中为 日指标，调度账期格式不为 'yyyyMMdd'");
            }
        }else if (acctDate.length()==8){
            if ("M".equals(kpiType)){
                throw new MyException("\n指标调度错误或指标配置错误，该指标在配置表中为 月指标，调度账期格式不为 'yyyyMM'");
            }
        }else {
            throw new MyException("\n指标调度错误：\n调度指标加工流程账期格式错误，应当为 'yyyyMMdd' 或 'yyyyMM' ");
        }
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 检测计算指标配置是否正确
     * @Date: 2022/11/27 11:25
     * @Param: []
     * @return: void
     */
    public static void checkKpiConf(HashMap<String,String> kpiConfMap){
        //配置表中的指标类型
        String compKpiType = kpiConfMap.get("COMP_KPI_TYPE");
        //默认的计算指标公式配置列
        String srcValueCol = kpiConfMap.get("SRC_VALUE_COL");
        if ("JS".equals(compKpiType)){
            if (srcValueCol.isEmpty()){
                throw new MyException("\n指标配置错误：\n计算指标的计算公式应当在 SRC_VALUE_COL列 进行配置");
            } else if (!(srcValueCol.contains("{")&&srcValueCol.contains("}"))){
                throw new MyException("\n指标配置错误：\n计算指标的 SRC_VALUE_COL列 应当配置正确的计算公式n");
            }
        } else if ("B".equals(compKpiType)){
            if (srcValueCol.isEmpty()){
                throw new MyException("\n指标配置错误：\n基础指标应当在 SRC_VALUE_COL列 配置指标计算的度量字段 ");
            } else if (srcValueCol.contains("{")||srcValueCol.contains("}")){
                throw new MyException("\n指标配置错误：\n指标类型为基础指标，疑似配成了计算指标。");
            }
        } else {
            throw new MyException("\n指标配置错误：\n指标类型配置错误！！\n（ COMP_KPI_TYPE 只支持 JS 和 B，并严格区分大小写）");
        }

    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 检测配置表中的配置信息是否为大写
     * @Date: 2022/11/27 11:48
     * @Param: [kpiConfMap]
     * @return: void
     */
    public static void checkUpperCase(HashMap<String,String> kpiConfMap){
        List<String> list = new ArrayList<>();
        CheckColEnum.toList().stream().forEach(col->{
            if (!CommonFun.isUpperCase(kpiConfMap.get(col))){
                list.add(col);
            }
        });
        if (list.size()>0){
            StringBuilder sql = new StringBuilder();
            for (int i=0;i<list.size();i++){
                if (i<list.size()-1){
                    sql.append(list.get(i)).append(" = UPPER(").append(list.get(i)).append("),\n");
                }else {
                    sql.append(list.get(i)).append(" = UPPER(").append(list.get(i)).append(")");
                }
            }
            sql.append("\nWHERE KPI_CODE = '").append(kpiConfMap.get("KPI_CODE")).append("';");
            throw new MyException("\n指标配置问题：\n"+list.toString()+"\n以上列应当使用大写字母，可尝试通过以下语句修复：\n-------------------\n"+"UPDATE DIM_COMP_KPI \nSET "+sql.toString()+"\n-------------------\n");
        }
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 检测必要配置信息不为空
     * @Date: 2022/11/27 22:54
     * @Param: [kpiConfMap]
     * @return: void
     */
    public static void checkNotNull(HashMap<String,String> kpiConfMap){
        List<String> list = new ArrayList<>();
        List<String> colList = CheckColEnum.toList();
        colList.stream().forEach(col->{
            if (StringUtils.isNullOrEmpty(kpiConfMap.get(col))||kpiConfMap.get(col).trim().isEmpty()||!(kpiConfMap.get(col) instanceof String)){
                list.add(col);
            }
        });
        if (list.size()>0){
            StringBuilder sql = new StringBuilder();
            for (int i = 0;i<list.size();i++){
                if (i<list.size()-1){
                    sql.append(list.get(i)).append(" = ").append("'',\n\t");
                }else {
                    sql.append(list.get(i)).append(" = ").append("''\n");
                }
            }
            sql.append("WHERE KPI_CODE = '").append(kpiConfMap.get("KPI_CODE")).append("'\n;");
            throw new MyException("\n指标配置问题：\n"+list.toString()+"\n以上列不能为空，可尝试通过以下语句修复：\n-------------------\n"+"UPDATE DIM_COMP_KPI \nSET "+sql.toString()+"\n-------------------\n");
        }
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 检测当前指标是否有效
     * @Date: 2022/11/27 23:28
     * @Param: []
     * @return: void
     */
    public static void checkIsValid(HashMap<String,String> kpiConfMap){
        if (!"1".equals(kpiConfMap.get("IS_VALID"))){
            throw new MyException("\n提示：\n"+kpiConfMap.get("KPI_CODE")+"指标在配置表中已设置为无效指标，若仍要加工该指标，可先使用如下语句将其设置为有效：\n-------------------\n"+
                    "UPDATE DIM_COMP_KPI \nSET IS_VALID = '1' \nWHERE KPI_CODE = '"+kpiConfMap.get("KPI_CODE")+"'\n;\n-------------------\n");
        }
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 检测ETL配置是否符合要求
     * @Date: 2022/11/28 0:55
     * @Param: [kpiConfMap, etlConfMap]
     * @return: void
     */
    public static void checkEtlCol(String acctDate,HashMap<String,String> kpiConfMap,HashMap<String,HashMap<Integer,String>> etlConfMap){
        String kpiCode = kpiConfMap.get("KPI_CODE");
        String dstTable = kpiConfMap.get("DST_ENTITY_NAME");
        HashMap<Integer,String> srcColMap = etlConfMap.get("srcColMap");
        HashMap<Integer,String> dstColMap = etlConfMap.get("dstColMap");
        List<String> list = new ArrayList<>();
        if (dstColList.size()>dstColMap.size()){
            dstColMap.keySet().stream().forEach(key->{
                list.add(dstColMap.get(key));
            });
            throw new MyException("\nETL配置错误：\n"+kpiCode+" 指标的目标表 "+dstTable+" 的维度字段\n"+ CommonFun.listrem(dstColList, list) + "\n在ETL配置表中未全部配置。");
        }else if (dstColList.size()<dstColMap.size()){
            dstColMap.keySet().stream().forEach(key->{
                list.add(dstColMap.get(key));
            });
            throw new MyException("\nETL配置错误：\n"+kpiCode+" 指标在ETL的目标表 "+dstTable+" 配置的维度字段\n"+ CommonFun.listrem(list, dstColList) + "\n在目标表中不存在。");
        }
        if (acctDate.length()==8){
            for (int i=0;i<srcColMap.size();i++){
                if (!srcColMap.containsValue("DATE_ID")){
                    throw new MyException("\nETL配置错误：\n日指标的ETL中没有配置 DATE_ID 字段");
                }
            }
        }else {
            for (int i=0;i<srcColMap.size();i++){
                if (srcColMap.containsValue("DATE_ID")){
                    throw new MyException("\nETL配置错误：\n月指标的ETL中不应该使用 DATE_ID 字段");
                }
            }
        }
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 检测指标配置的来源表和目标表字段对应关系是否能自动匹配，能返回True，不能返回False
     * @Date: 2022/11/30 10:13
     * @Param: [kpiConfMap, conn]
     * @return: java.lang.Boolean
     */
    public static void checkRelationMap(HashMap<String,String> kpiConfMap,Properties confProp){
        Connection conn = MyJdbcUtils.getADBConnection(confProp);
        String srcTableSchema = kpiConfMap.get("SRC_ENTITY_OWNER");
        String dstTableSchema = kpiConfMap.get("DST_ENTITY_OWNER");
        String srcTable = kpiConfMap.get("SRC_ENTITY_NAME");
        String dstTable = kpiConfMap.get("DST_ENTITY_NAME");
        List<String> srcColumnList = CommonFun.getColumDesc(srcTableSchema,srcTable, conn);
        List<String> dstColumnList = CommonFun.getColumDesc(dstTableSchema,dstTable, conn);
        List<String> rmList = new ArrayList<>();
        if ("D".equals(kpiConfMap.get("COMP_KPI_DATA_TYPE"))){
            rmList.add("KPI_CODE");
            rmList.add("KPI_VALUE");
            rmList.add("D_LD_VALUE");
            rmList.add("M_TM_VALUE");
            rmList.add("D_LM_VALUE");
            rmList.add("M_LM_VALUE");
            rmList.add("D_LY_VALUE");
            rmList.add("M_LY_VALUE");
            rmList.add("M_DA_VALUE");
            rmList.add("M_LDA_VALUE");
            dstColumnList.removeAll(rmList);
        }else {
            rmList.add("KPI_CODE");
            rmList.add("KPI_VALUE");
            rmList.add("M_LM_VALUE");
            rmList.add("Y_TY_VALUE");
            rmList.add("M_LY_VALUE");
            rmList.add("Y_LY_VALUE");
            rmList.add("M_LY12_VALUE");
            dstColumnList.removeAll(rmList);
        }
        MyJdbcUtils.close(conn);
        if (srcColumnList.containsAll(dstColumnList)&&dstColumnList.size()>0){
            dstColList.clear();
            dstColList.addAll(dstColumnList);
            isRelationMap = Boolean.TRUE;
        }else {
            dstColList.clear();
            dstColList.addAll(dstColumnList);
            isRelationMap = Boolean.FALSE;
        }
    }


    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 检测Mc上的指标配置的来源表和目标表字段对应关系是否能自动匹配，能返回True，不能返回False
     * @Date 2023/4/5 18:42
     * @Param: [kpiConfMap, confProp]
     * @Return: void
     */
    public static void checkMcRelationMap(HashMap<String,String> kpiConfMap,Properties confProp){
        Connection conn = MyJdbcUtils.getADBConnection(confProp);
        String srcTableSchema = kpiConfMap.get("SRC_ENTITY_OWNER");
        String dstTableSchema = kpiConfMap.get("DST_ENTITY_OWNER");
        String srcTable = kpiConfMap.get("SRC_ENTITY_NAME");
        String dstTable = kpiConfMap.get("DST_ENTITY_NAME");
        List<String> srcColumnList = CommonFun.getMcColumDesc(srcTableSchema,srcTable, confProp);
        List<String> dstColumnList = CommonFun.getMcColumDesc(dstTableSchema,dstTable, confProp);
        List<String> rmList = new ArrayList<>();
        if ("D".equals(kpiConfMap.get("COMP_KPI_DATA_TYPE"))){
            rmList.add("KPI_CODE");
            rmList.add("KPI_VALUE");
            rmList.add("D_LD_VALUE");
            rmList.add("M_TM_VALUE");
            rmList.add("D_LM_VALUE");
            rmList.add("M_LM_VALUE");
            rmList.add("D_LY_VALUE");
            rmList.add("M_LY_VALUE");
            rmList.add("M_DA_VALUE");
            rmList.add("M_LDA_VALUE");
            dstColumnList.removeAll(rmList);
        }else {
            rmList.add("KPI_CODE");
            rmList.add("KPI_VALUE");
            rmList.add("M_LM_VALUE");
            rmList.add("Y_TY_VALUE");
            rmList.add("M_LY_VALUE");
            rmList.add("Y_LY_VALUE");
            rmList.add("M_LY12_VALUE");
            dstColumnList.removeAll(rmList);
        }
        if (srcColumnList.containsAll(dstColumnList)&&dstColumnList.size()>0){
            dstColList.clear();
            dstColList.addAll(dstColumnList);
            isRelationMap = Boolean.TRUE;
        }else {
            dstColList.clear();
            dstColList.addAll(dstColumnList);
            isRelationMap = Boolean.FALSE;
        }
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 检测指标配置的来源表和目标表字段对应关系是否能自动匹配，能返回True，不能返回False
     * @Date: 2022/11/30 10:13
     * @Param: [kpiConfMap, conn]
     * @return: java.lang.Boolean
     */
    public static void checkRelationMap(HashMap<String,String> kpiConfMap,Connection conn){
        String srcTableSchema = kpiConfMap.get("SRC_ENTITY_OWNER");
        String dstTableSchema = kpiConfMap.get("DST_ENTITY_OWNER");
        String srcTable = kpiConfMap.get("SRC_ENTITY_NAME");
        String dstTable = kpiConfMap.get("DST_ENTITY_NAME");
        List<String> srcColumnList = CommonFun.getColumDesc(srcTableSchema,srcTable, conn);
        List<String> dstColumnList = CommonFun.getColumDesc(dstTableSchema,dstTable, conn);
        List<String> rmList = new ArrayList<>();
        if ("D".equals(kpiConfMap.get("COMP_KPI_DATA_TYPE"))){
            rmList.add("KPI_CODE");
            rmList.add("KPI_VALUE");
            rmList.add("D_LD_VALUE");
            rmList.add("M_TM_VALUE");
            rmList.add("D_LM_VALUE");
            rmList.add("M_LM_VALUE");
            rmList.add("D_LY_VALUE");
            rmList.add("M_LY_VALUE");
            rmList.add("M_DA_VALUE");
            rmList.add("M_LDA_VALUE");
            dstColumnList.removeAll(rmList);
        }else {
            rmList.add("KPI_CODE");
            rmList.add("KPI_VALUE");
            rmList.add("M_LM_VALUE");
            rmList.add("Y_TY_VALUE");
            rmList.add("M_LY_VALUE");
            rmList.add("Y_LY_VALUE");
            rmList.add("M_LY12_VALUE");
            dstColumnList.removeAll(rmList);
        }
        if (srcColumnList.containsAll(dstColumnList)&&dstColumnList.size()>0){
            dstColList.clear();
            dstColList.addAll(dstColumnList);
            isRelationMap = Boolean.TRUE;
        }else {
            isRelationMap = Boolean.FALSE;
        }
    }
}